﻿<%@ WebHandler Language="C#" Class="GZSpecCapacityDashBoardV2" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 工装工具厂--工序能力看板
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.5.15
 */

public class GZSpecCapacityDashBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    TreeObject t = new TreeObject();
    List<TreeObject> treeNodes = new List<TreeObject>();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getMESOrginazition":
                        r = getMESOrginazition(jObject);//查询MES系统组织结构
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSpecItems":
                        r = getSpecItems(jObject);//查询已维护工序项
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "checkIsExist":
                        r = checkIsExist(jObject);//检查待新增工序是否已存在
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "insertData":
                        r = insertData(jObject);//新增工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deleteData":
                        r = deleteData(jObject);//删除工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "changeStatus":
                        r = changeStatus(jObject);//切换启用状态
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "updateData":
                        r = updateData(jObject);//修改工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChartData":
                        r = getChartData(jObject);//查询柱状图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getData":
                        r = getData(jObject);//主页面表格数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChartDetail":
                        r = getChartDetail(jObject);//已开卡明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询MES组织结构(分厂/工区)
    Result getMESOrginazition(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT te.id,te.departmentname label,te.orgtype,CASE WHEN te.parentid IS NOT NULL THEN te.parentid ELSE '0' END parentid
FROM (
  SELECT w.workcenterid ID,w.workcentername departmentname,w.factoryid parentid, '工区' orgtype
  FROM workcenter w
  LEFT JOIN factory f ON f.factoryid = w.factoryid
  WHERE w.factoryid IS NOT NULL AND f.factoryname IS NOT NULL AND w.notes IS NULL AND w.wctype IS NULL
  
  UNION ALL
  SELECT f.factoryid ID,f.factoryname departmentname,NULL parentid,'分厂' orgtype
  FROM mes_factory_mapping f  WHERE f.factorycode = '{0}'
) te
";
        DataTable dt = dal.GetDataTable(string.Format(sql, jObject["factoryCode"].ToString()));
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        treeNodes = t.BindTree(dt, null, "0");
        r.data = treeNodes;
        r.total = Convert.ToInt32(0);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询已维护工序项
    Result getSpecItems(JObject jObject)
    {
        string sql = @"
SELECT t.id,t.specname,f.factoryname,t.factoryid,w.workcentername,t.workcenterid,t.orgtype,t.threshold,t.status,
       e.fullname createemployee,t.createdate,
       e2.fullname lastchangeemployee,t.lastchangedate
FROM gzspecworktimemaint t
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN employee e ON e.employeeid = t.createemployee
LEFT JOIN employee e2 ON e2.employeeid = t.lastchangeemployee
WHERE 1=1";
        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND t.specname LIKE '%{0}%'", jObject["specname"].ToString());
        }

        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 1)
        {
            sql += string.Format(" AND t.factoryid = '{0}'", selectOrg[0].ToString());
        }
        else if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND t.factoryid = '{0}' AND t.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
        }

        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //校验是否已存在
    Result checkIsExist(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string factoryid = "", workcenterid = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }

        string sql = @"SELECT * FROM gzspecworktimemaint t WHERE t.specname = '{0}' AND t.factoryid='{1}' AND NVL(t.workcenterid,'0')='{2}'";
        if (jObject["id"] != null && jObject["id"].ToString() != "")
        {
            sql += string.Format(" AND t.id <> '{0}'", jObject["id"].ToString());
        }
        sql = string.Format(sql, jObject["specname"].ToString().Trim(), factoryid, workcenterid != "" ? workcenterid : "0");
        DataTable dt = dal.GetDataTable(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //新增数据
    Result insertData(JObject jObject)
    {
        string factoryid = "", workcenterid = "", orgtype = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            orgtype = "工区级";
        }
        else
        {
            factoryid = selectOrg[0].ToString();
            orgtype = "分厂级";
        }

        string sql = @"
INSERT INTO gzspecworktimemaint(id,specname,factoryid,workcenterid,orgtype,threshold,status,createemployee,createdate)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}',1,'{6}',sysdate)";

        sql = string.Format(sql,
                            Guid.NewGuid().ToString(),//唯一id
                            jObject["specname"].ToString().Trim(),//工序名称
                            factoryid,//分厂
                            workcenterid,//工区
                            orgtype,//颗粒度
                            jObject["threshold"].ToString(),//阈值
                            jObject["employeeid"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除数据
    Result deleteData(JObject jObject)
    {
        string sql = string.Format("DELETE gzspecworktimemaint t WHERE t.id = '{0}'", jObject["id"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //切换启用状态
    Result changeStatus(JObject jObject)
    {
        string sql = @"UPDATE gzspecworktimemaint t SET t.status='{1}',t.lastchangedate = sysdate,t.lastchangeemployee='{2}' WHERE t.id='{0}'";
        int status = 0;
        if (jObject["status"] != null)
        {
            status = jObject["status"].ToString() == "True" ? 1 : 0;
        }

        sql = string.Format(sql, jObject["id"].ToString(), status, jObject["employeeid"].ToString());
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //修改工序
    Result updateData(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string factoryid = "", workcenterid = "", orgtype = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            orgtype = "工区级";
        }
        else
        {
            factoryid = selectOrg[0].ToString();
            orgtype = "分厂级";
        }

        string sql = @"
UPDATE gzspecworktimemaint t 
SET t.specname = '{1}',t.factoryid = '{2}',t.workcenterid = '{3}',t.orgtype = '{4}',t.threshold = '{5}',
    t.lastchangeemployee = '{6}',t.lastchangedate = sysdate
WHERE t.id = '{0}'";

        sql = string.Format(sql,
                            jObject["id"].ToString(),//唯一id
                            jObject["specname"].ToString().Trim(),//工序名称
                            factoryid,//分厂
                            workcenterid,//工区
                            orgtype,//组织结构
                            jObject["threshold"].ToString(),//阈值
                            jObject["employeeid"].ToString()//修改人
                            );
        int count = dal.ExecuteSql(sql);
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询柱状图数据
    Result getChartData(JObject jObject)
    {
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string startdate = selectdate[0].ToString();
        string enddate = selectdate[1].ToString();
        string factoryid = "", workcenterid = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT t.threshold,t.specname,SUM(m.worktime) worktime
FROM gzspecworktimemaint t
LEFT JOIN 
(
  SELECT cci.containerid topcontainerid,cci.containername topcontainername,cci.containerid,cci.containername,s.opdesc,sd.durationperunit,cci.qty,
         NVL(sd.durationperunit,0) * NVL(cci.qty,0) worktime
  FROM containercurrentinfo cci
  LEFT JOIN product p ON p.productid = cci.productid
  INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
  LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
  LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
  LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
  LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
  LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
  WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        sql += @"
  UNION ALL
  SELECT cci2.containerid topcontainerid,cci2.containername topcontainername,cci2.containerid,cci2.containername,s.opdesc,sd.durationperunit,cci2.qty,
         NVL(sd.durationperunit,0) * NVL(cci2.qty,0) worktime
  FROM containercurrentinfo cci
  LEFT JOIN product p ON p.productid = cci.productid
  INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
  LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
  LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
  LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
  LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
  LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
  LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
  LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
  WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}' AND cci2.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci2.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }
        sql += @"
) m ON t.specname = m.opdesc
WHERE t.status = 1 ";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND t.factoryid = '{0}' AND t.workcenterid = '{1}' AND t.orgtype = '工区级'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND t.factoryid = '{0}' AND t.orgtype = '分厂级'", factoryid);
        }

        sql += @"
GROUP BY t.threshold,t.specname
ORDER BY t.specname";
        sql = string.Format(sql, startdate, enddate);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = dt.Rows.Count;
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //主页面表格数据
    Result getData(JObject jObject)
    {
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string startdate = selectdate[0].ToString(), enddate = selectdate[1].ToString();
        string factoryid = "", workcenterid = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT cci.productname,cci.containername,cci.fromworkcentername,od.startdate,od.enddate,cci.qty
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        sql += @" union all
SELECT cci2.productname,cci2.containername,cci2.fromworkcentername,od.startdate,od.enddate,cci2.qty
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername like '%{0}%'", jObject["containername"].ToString());
        }

        sql = string.Format(sql, startdate, enddate);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        //查询所选组织结构已维护工序
        sql = @"SELECT gz.specname FROM gzspecworktimemaint gz WHERE gz.status = 1";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND gz.factoryid = '{0}' AND gz.workcenterid = '{1}' AND gz.orgtype = '工区级'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND gz.factoryid = '{0}' AND gz.orgtype = '分厂级'", factoryid);
        }

        DataTable dtSpec = dal.GetDataTable(sql);
        foreach (DataRow dr in dtSpec.Rows)
        {
            dtResult.Columns.Add(dr["specname"].ToString(), typeof(object));
        }

        //查批次开卡工时
        sql = @"
SELECT t.topcontainername containername,t.opdesc,SUM(t.worktime) worktime
FROM
(
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,cci.containerid,cci.containername,s.opdesc,sd.durationperunit,cci.qty,
           NVL(sd.durationperunit,0) * NVL(cci.qty,0) worktime
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        sql += @"
    UNION ALL   
    SELECT cci2.containerid topcontainerid,cci2.containername topcontainername,cci2.containerid,cci2.containername,s.opdesc,sd.durationperunit,cci2.qty,
           NVL(sd.durationperunit,0) * NVL(cci2.qty,0) worktime
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}' AND cci2.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci2.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        sql += @"
) t
GROUP BY t.topcontainername,t.opdesc";

        sql = string.Format(sql, startdate, enddate);
        DataTable dtWorktime = dal.GetDataTable(sql);

        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = 7; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("containername = '{0}' AND opdesc='{1}'", dr["containername"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dtWorktime.Select(filter);
                DataTable dtPlanSpec;
                if (drList.Length > 0)
                {
                    dtPlanSpec = drList.CopyToDataTable();
                    foreach (DataColumn col in dtPlanSpec.Columns)
                    {
                        col.ColumnName = col.ColumnName.ToLower();
                    }
                    dr[dtResult.Columns[i].ColumnName] = dtPlanSpec;
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = new DataTable();
                }
            }
        }

        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //已开卡明细
    Result getChartDetail(JObject jObject)
    {
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string startdate = selectdate[0].ToString(), enddate = selectdate[1].ToString();
        string factoryid = "", workcenterid = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT od.startdate,od.enddate,cci.containerid,cci.containername,cci.qty,csd.reportqty,cci.workflowstepname currentspec,s.opdesc,ws.workflowstepname,sd.durationperunit,
       csd.dispatchemployeename,csd.dispatchdate,csd.reportemployeename,csd.reportdate,csd.checkemployeename,csd.checkdate,
       CASE WHEN csd.checkdate IS NOT NULL THEN '已完工'
            WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csd.dispatchdate IS NULL THEN '待派工' 
       END status
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND s.opdesc = '{0}'", jObject["specname"].ToString());
        }

        sql += @"           
UNION ALL            
SELECT od.startdate,od.enddate,cci2.containerid,cci2.containername,cci2.qty,csd.reportqty,cci2.workflowstepname currentspec,s.opdesc,ws.workflowstepname,sd.durationperunit,
       csd.dispatchemployeename,csd.dispatchdate,csd.reportemployeename,csd.reportdate,csd.checkemployeename,csd.checkdate,
       CASE WHEN csd.checkdate IS NOT NULL THEN '已完工'
            WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csd.dispatchdate IS NULL THEN '待派工' 
       END status
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}' AND cci2.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci2.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername LIKE '%{0}%'", jObject["containername"].ToString());
        }

        if (jObject["specname"] != null && jObject["specname"].ToString() != "")
        {
            sql += string.Format(" AND s.opdesc = '{0}'", jObject["specname"].ToString());
        }

        sql = string.Format(sql, startdate, enddate);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
        string startdate = selectdate[0].ToString();
        string enddate = selectdate[1].ToString();
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string factoryid = "", workcenterid = "";
        if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT cci.productname,cci.containername,cci.fromworkcentername,od.startdate,od.enddate,cci.qty
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        DataTable dtResult = dal.GetDataTable(string.Format(sql, startdate, enddate));

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        //查询所选组织结构已维护工序
        sql = @"SELECT gz.specname FROM gzspecworktimemaint gz WHERE gz.status = 1";
        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND gz.factoryid = '{0}' AND gz.workcenterid = '{1}' AND gz.orgtype = '工区级'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND gz.factoryid = '{0}' AND gz.orgtype = '分厂级'", factoryid);
        }

        DataTable dtSpec = dal.GetDataTable(sql);
        foreach (DataRow dr in dtSpec.Rows)
        {
            dtResult.Columns.Add(dr["specname"].ToString(), typeof(object));
        }

        //查批次开卡工时
        sql = @"
SELECT t.topcontainername containername,t.opdesc,SUM(t.worktime) worktime
FROM
(
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,cci.containerid,cci.containername,s.opdesc,sd.durationperunit,cci.qty,
           NVL(sd.durationperunit,0) * NVL(cci.qty,0) worktime
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}' AND cci.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
    UNION ALL   
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,cci2.containerid,cci2.containername,s.opdesc,sd.durationperunit,cci2.qty,
           NVL(sd.durationperunit,0) * NVL(cci2.qty,0) worktime
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND od.enddate >= to_date('{0}','yyyy/mm/dd') AND od.enddate <= to_date('{1}','yyyy/mm/dd')";

        if (selectOrg.Count == 2)
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}' AND cci2.fromworkcenterid = '{1}'", factoryid, workcenterid);
        }
        else
        {
            sql += string.Format(" AND cci2.originalfactoryid = '{0}'", factoryid);
        }

        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci2.productname = '{0}'", jObject["productname"].ToString());
        }

        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci2.containername = '{0}'", jObject["containername"].ToString());
        }

        sql += @"
) t
GROUP BY t.topcontainername,t.opdesc";

        DataTable dtWorktime = dal.GetDataTable(string.Format(sql, startdate, enddate));
        foreach (DataRow dr in dtResult.Rows)
        {
            for (int i = 6; i < dtResult.Columns.Count; i++)
            {
                string filter = string.Format("containername = '{0}' AND opdesc='{1}'", dr["containername"], dtResult.Columns[i].ColumnName);
                DataRow[] drList = dtWorktime.Select(filter);
                if (drList.Length > 0)
                {
                    dr[dtResult.Columns[i].ColumnName] = drList[0]["worktime"].ToString();
                }
                else
                {
                    dr[dtResult.Columns[i].ColumnName] = "";
                }
            }
        }

        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["containername"].Caption = "零件/批次号";
        dtResult.Columns["fromworkcentername"].Caption = "主制工区";
        dtResult.Columns["qty"].Caption = "在制数";
        dtResult.Columns["startdate"].Caption = "开始时间";
        dtResult.Columns["enddate"].Caption = "结束时间";
        ExportHelper.ExportExcel(dtResult, "工序能力看板导出");
    }
}